using AutoMapper;
using MediatR;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using ThreeTeams.Domain.ThreeTeams.Domain.Entities;
using WMS.Api.Application.Commands;
using WMS.Common;
using WMS.Infrastructure;

namespace WMS.Api.Application.CommandHandlers
{
    /// <summary>
    /// 导入产品命令处理程序
    /// </summary>
    public class ImportProductCommandHandler : IRequestHandler<ImportProductCommand, ApiResult<ImportResult>>
    {
        private readonly IBaseRepository<Product> _productRepository;
        private readonly IMapper _mapper;

        public ImportProductCommandHandler(IBaseRepository<Product> productRepository, IMapper mapper)
        {
            _productRepository = productRepository;
            _mapper = mapper;
        }

        public async Task<ApiResult<ImportResult>> Handle(ImportProductCommand request, CancellationToken cancellationToken)
        {
            var result = new ApiResult<ImportResult>("导入产品");
            var importResult = new ImportResult();

            try
            {
                if (request.File == null || request.File.Length == 0)
                {
                    result.code = StatusCodeEnum.ParamError;
                    result.msg = "请选择要导入的Excel文件";
                    return result;
                }

                // 检查文件扩展名
                var fileExtension = Path.GetExtension(request.File.FileName).ToLower();
                if (fileExtension != ".xlsx" && fileExtension != ".xls")
                {
                    result.code = StatusCodeEnum.ParamError;
                    result.msg = "请上传Excel文件(.xlsx或.xls)";
                    return result;
                }

                using var stream = request.File.OpenReadStream();
                IWorkbook workbook;

                // 根据文件扩展名创建不同的工作簿对象
                if (fileExtension == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }

                var sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    result.code = StatusCodeEnum.ParamError;
                    result.msg = "Excel文件中没有工作表";
                    return result;
                }

                // 获取表头行
                var headerRow = sheet.GetRow(0);
                if (headerRow == null)
                {
                    result.code = StatusCodeEnum.ParamError;
                    result.msg = "Excel文件中没有表头行";
                    return result;
                }

                // 验证表头
                var requiredHeaders = new List<string> { "产品名称", "SKU", "入库价格", "出库价格" };
                var headerIndices = new Dictionary<string, int>();

                for (int i = 0; i < headerRow.LastCellNum; i++)
                {
                    var cell = headerRow.GetCell(i);
                    if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
                    {
                        headerIndices[cell.ToString()] = i;
                    }
                }

                foreach (var header in requiredHeaders)
                {
                    if (!headerIndices.ContainsKey(header))
                    {
                        result.code = StatusCodeEnum.ParamError;
                        result.msg = $"Excel文件中缺少必要的列：{header}";
                        return result;
                    }
                }

                // 读取数据行
                var products = new List<Product>();
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }

                    try
                    {
                        // 读取必填字段
                        var productNameCell = row.GetCell(headerIndices["产品名称"]);
                        var skuCell = row.GetCell(headerIndices["SKU"]);
                        var purchasePriceCell = row.GetCell(headerIndices["入库价格"]);
                        var sellingPriceCell = row.GetCell(headerIndices["出库价格"]);

                        if (productNameCell == null || skuCell == null || purchasePriceCell == null || sellingPriceCell == null)
                        {
                            importResult.FailCount++;
                            importResult.ErrorMessages.Add($"第{i + 1}行：必填字段不能为空");
                            continue;
                        }

                        var productName = productNameCell.ToString();
                        var sku = skuCell.ToString();

                        if (string.IsNullOrEmpty(productName) || string.IsNullOrEmpty(sku))
                        {
                            importResult.FailCount++;
                            importResult.ErrorMessages.Add($"第{i + 1}行：产品名称和SKU不能为空");
                            continue;
                        }

                        // 检查SKU是否已存在
                        var existingProduct = await _productRepository.GetNewValueAsync(p => p.SKU == sku);
                        if (existingProduct != null)
                        {
                            importResult.FailCount++;
                            importResult.ErrorMessages.Add($"第{i + 1}行：SKU '{sku}' 已存在");
                            continue;
                        }

                        // 解析价格
                        if (!TryGetCellValueAsLong(purchasePriceCell, out long purchasePrice))
                        {
                            importResult.FailCount++;
                            importResult.ErrorMessages.Add($"第{i + 1}行：入库价格格式不正确");
                            continue;
                        }

                        if (!TryGetCellValueAsLong(sellingPriceCell, out long sellingPrice))
                        {
                            importResult.FailCount++;
                            importResult.ErrorMessages.Add($"第{i + 1}行：出库价格格式不正确");
                            continue;
                        }

                        // 创建产品对象
                        var product = new Product
                        {
                            ProductName = productName,
                            SKU = sku,
                            PurchasePrice = purchasePrice,
                            SellingPrice = sellingPrice
                        };

                        // 读取可选字段
                        if (headerIndices.ContainsKey("规格"))
                        {
                            var cell = row.GetCell(headerIndices["规格"]);
                            product.Specification = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("库存预警数量"))
                        {
                            var cell = row.GetCell(headerIndices["库存预警数量"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int stockAlert))
                            {
                                product.StockAlert = stockAlert;
                            }
                        }

                        if (headerIndices.ContainsKey("重量"))
                        {
                            var cell = row.GetCell(headerIndices["重量"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int weight))
                            {
                                product.Weight = weight;
                            }
                        }

                        if (headerIndices.ContainsKey("品牌"))
                        {
                            var cell = row.GetCell(headerIndices["品牌"]);
                            product.Brand = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("型号"))
                        {
                            var cell = row.GetCell(headerIndices["型号"]);
                            product.Model = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("英文名称"))
                        {
                            var cell = row.GetCell(headerIndices["英文名称"]);
                            product.EnglishName = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("海关编码"))
                        {
                            var cell = row.GetCell(headerIndices["海关编码"]);
                            product.CustomsCode = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("申报价格"))
                        {
                            var cell = row.GetCell(headerIndices["申报价格"]);
                            if (cell != null && TryGetCellValueAsLong(cell, out long declaredPrice))
                            {
                                product.DeclaredPrice = declaredPrice;
                            }
                        }

                        if (headerIndices.ContainsKey("长度"))
                        {
                            var cell = row.GetCell(headerIndices["长度"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int length))
                            {
                                product.Length = length;
                            }
                        }

                        if (headerIndices.ContainsKey("宽度"))
                        {
                            var cell = row.GetCell(headerIndices["宽度"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int width))
                            {
                                product.Width = width;
                            }
                        }

                        if (headerIndices.ContainsKey("高度"))
                        {
                            var cell = row.GetCell(headerIndices["高度"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int height))
                            {
                                product.Height = height;
                            }
                        }

                        if (headerIndices.ContainsKey("状态"))
                        {
                            var cell = row.GetCell(headerIndices["状态"]);
                            if (cell != null && TryGetCellValueAsInt(cell, out int status))
                            {
                                product.Status = status;
                            }
                        }

                        if (headerIndices.ContainsKey("备注"))
                        {
                            var cell = row.GetCell(headerIndices["备注"]);
                            product.Notes = cell?.ToString();
                        }

                        if (headerIndices.ContainsKey("图片"))
                        {
                            var cell = row.GetCell(headerIndices["图片"]);
                            var photo = cell?.ToString();
                            if (!string.IsNullOrEmpty(photo))
                            {
                                product.Photo = photo;
                            }
                        }

                        // 设置默认值
                        product.TeamId = 1; // 默认团队ID
                        product.CategoryId = 1; // 默认类别ID
                        product.ProductTypeId = 1; // 默认产品类型ID

                        products.Add(product);
                        importResult.SuccessCount++;
                    }
                    catch (Exception ex)
                    {
                        importResult.FailCount++;
                        importResult.ErrorMessages.Add($"第{i + 1}行：处理数据时出错：{ex.Message}");
                    }
                }

                // 批量保存产品
                if (products.Count > 0)
                {
                    foreach (var product in products)
                    {
                        await _productRepository.AddNewAsync(product);
                    }
                }

                result.Success(importResult);
            }
            catch (Exception ex)
            {
                result.code = StatusCodeEnum.Fail;
                result.msg = $"导入产品失败：{ex.Message}";
            }

            return result;
        }

        private bool TryGetCellValueAsInt(ICell cell, out int value)
        {
            value = 0;
            try
            {
                if (cell.CellType == CellType.Numeric)
                {
                    value = (int)cell.NumericCellValue;
                    return true;
                }
                else if (cell.CellType == CellType.String && int.TryParse(cell.StringCellValue, out value))
                {
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        private bool TryGetCellValueAsLong(ICell cell, out long value)
        {
            value = 0;
            try
            {
                if (cell.CellType == CellType.Numeric)
                {
                    value = (long)cell.NumericCellValue;
                    return true;
                }
                else if (cell.CellType == CellType.String && long.TryParse(cell.StringCellValue, out value))
                {
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }
    }
} 